package com.tools.db.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.tools.common.exception.SQLRuntimeException;
import com.tools.common.object.C;
import com.tools.common.object.Note;
import javax.servlet.ServletRequestEvent;
import javax.servlet.ServletRequestListener;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 线程安全的 JDBC 包装操作类
 * 其实现了了 Servlet 的监听器接口，
 * 在 Spring 的项目中将其配置成 Bean
 * 可自动回收 Connection 数据库连接对象
 * */
@Note("线程安全的 JDBC 包装操作类，其实现了了 Servlet 的监听器接口" +
        "在 Spring 的项目中将其配置成 Bean，可自动回收 Connection 数据库连接对象")
public final class JdbcPlus implements ServletRequestListener {

    @Note("Druid 数据源")
    private final DruidDataSource dataSource;

    @Note("使用 ThreadLocal 为每个线程缓存一个连接副本，避免线程安全问题和节省连接开支")
    private final ThreadLocal<Connection> threadLocal;

    /* *******************************************************************************************
     *
     *          构造器
     *
     * *******************************************************************************************
     * */

    public JdbcPlus(DBSource source) {
        this.dataSource = JdbcKit.newDataSource(source);
        this.threadLocal = new ThreadLocal<>();
    }

    public JdbcPlus(DruidDataSource dataSource) {
        if(dataSource == null) throw new NullPointerException("入参的 DruidDataSource 不能为 null");
        this.dataSource = dataSource;
        this.threadLocal = new ThreadLocal<>();
    }

    /* *******************************************************************************************
     *
     *          重要方法
     *
     * *******************************************************************************************
     * */

    @Note("使用 ThreadLocal 从连接池中获取 Connection 连接对象")
    public Connection getConnection() {
        Connection connection = this.threadLocal.get();
        if(connection == null) {
           connection = JdbcKit.openConnection(dataSource);
            this.threadLocal.set(connection);
        }
        return connection;
    }

    @Note("本类继承自 Servlet 请求监听器，在一次请求被初始化时执行，但要将其配置成 Bean 交给 Spring 容器管理")
    @Override
    public void requestInitialized(ServletRequestEvent sre) {
        this.close();
    }


    @Note("本类继承自 Servlet 请求监听器，在一次请求结束之前执行该方法，但要将其配置成 Bean 交给 Spring 容器管理")
    @Override
    public void requestDestroyed(ServletRequestEvent sre) {
        this.close();
    }


    @Note("移除 ThreadLocal 维护的 Connection 连接实例，并将其回收到连接池中")
    public void close() {
        Connection connection = this.threadLocal.get();
        this.threadLocal.set(null);
        this.threadLocal.remove();
        if(connection != null) {
            JdbcKit.close(connection);
        }
    }



    /* *******************************************************************************************
     *
     *          需要手动 close 回收 Connection 的功能方法
     *
     * *******************************************************************************************
     * */

    public int insert(String sql, Object... parameters) {
        return private_executeUpdate(sql, parameters);
    }

    public int update(String sql, Object... parameters) {
        return private_executeUpdate(sql, parameters);
    }

    public int delete(String sql, Object... parameters) {
        return private_executeUpdate(sql, parameters);
    }

    public long largeInsert(String sql, Object... parameters) {
        return private_executeLargeUpdate(sql, parameters);
    }

    public long largeUpdate(String sql, Object... parameters) {
        return private_executeLargeUpdate(sql, parameters);
    }

    public long largeDelete(String sql, Object... parameters) {
        return private_executeLargeUpdate(sql, parameters);
    }

    @Note("查询单条记录，然后封装结果成指定的类型返回")
    public <T> T select(String sql, C<T> resultType, Object... parameters) {
        T result;
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = JdbcKit.newPreparedStatement(connection, sql, parameters);
            resultSet = JdbcKit.executeQuery(statement);
            return JdbcKit.processOneResult(resultSet, resultType);
        } catch (Exception e) {
            throw new SQLRuntimeException(e);
        } finally {
            JdbcKit.close(resultSet);
            JdbcKit.close(statement);
        }
    }

    @Note("查询多条记录，然后封装结果成指定的类型的 ArrayList 集合返回")
    public <T> List<T> selectList(String sql, C<T> resultType, Object... parameters) {
        List<T> results;
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = JdbcKit.newPreparedStatement(connection, sql, parameters);
            resultSet = JdbcKit.executeQuery(statement);
            return JdbcKit.processMultipleResult(resultSet, resultType);
        } catch (Exception e) {
            throw new SQLRuntimeException(e);
        } finally {
            JdbcKit.close(resultSet);
            JdbcKit.close(statement);
        }
    }

    @Note("查询单条记录，然后封装结果成指定的类型返回")
    public <T> T select(String sql, ResultSetExtractor<T> extractor, Object... parameters) {
        Connection connection = getConnection();
        PreparedStatement statement = JdbcKit.newPreparedStatement(connection, sql, parameters);
        ResultSet resultSet = JdbcKit.executeQuery(statement);
        try {
            return extractor.extractData(resultSet);
        } catch (SQLException e) {
            throw new SQLRuntimeException(e);
        } finally {
            JdbcKit.close(resultSet);
            JdbcKit.close(statement);
        }
    }

    @Note("查询多条记录，然后封装结果成指定的类型的 ArrayList 集合返回")
    public <T> List<T> selectList(String sql, RowMapper<T> componentMapper, Object... parameters) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = JdbcKit.newPreparedStatement(connection, sql);
            resultSet = JdbcKit.executeQuery(statement);
            int rowNum = 1;
            List<T> results = new ArrayList<>();
            while (resultSet.next()) {
                T bean = componentMapper.mapRow(resultSet, rowNum);
                results.add(bean);
                rowNum++;
            }
            return results;
        } catch (Exception e) {
            throw new SQLRuntimeException(e);
        } finally {
            JdbcKit.close(resultSet);
            JdbcKit.close(statement);
        }
    }


    /* *******************************************************************************************
     *
     *          自动 close 回收 Connection 的功能方法
     *
     * *******************************************************************************************
     * */

    public int insert$(String sql, Object... parameters) {
        try {
            return private_executeUpdate(sql, parameters);
        } finally {
            this.close();
        }
    }

    public int update$(String sql, Object... parameters) {
        try {
            return private_executeUpdate(sql, parameters);
        } finally {
            this.close();
        }
    }

    public int delete$(String sql, Object... parameters) {
        try {
            return private_executeUpdate(sql, parameters);
        } finally {
            this.close();
        }
    }

    public long largeInsert$(String sql, Object... parameters) {
        try {
            return private_executeLargeUpdate(sql, parameters);
        } finally {
            this.close();
        }
    }

    public long largeUpdate$(String sql, Object... parameters) {
        try {
            return private_executeLargeUpdate(sql, parameters);
        } finally {
            this.close();
        }
    }

    public long largeDelete$(String sql, Object... parameters) {
        try {
            return private_executeLargeUpdate(sql, parameters);
        } finally {
            this.close();
        }
    }

    public <T> T select$(String sql, C<T> resultType, Object... parameters) {
        try {
            return this.select(sql, resultType, parameters);
        } finally {
            this.close();
        }
    }


    public <T> List<T> selectList$(String sql, C<T> resultType, Object... parameters) {
        try {
            return this.selectList(sql, resultType, parameters);
        } finally {
            this.close();
        }
    }


    public <T> T select$(String sql, ResultSetExtractor<T> extractor, Object... parameters) {
        try {
            return this.select(sql, extractor, parameters);
        } finally {
            this.close();
        }
    }

    public <T> List<T> selectList$(String sql, RowMapper<T> componentMapper, Object... parameters) {
        try {
            return this.selectList(sql, componentMapper, parameters);
        } finally {
            this.close();
        }
    }


    /* *******************************************************************************************
     *
     *          私有逻辑
     *
     * *******************************************************************************************
     * */

    @Note("增删改执行逻辑")
    private int private_executeUpdate(String sql, Object... parameters) {
        Connection connection = this.getConnection();
        PreparedStatement statement = null;
        try {
            statement = JdbcKit.newPreparedStatement(connection, sql, parameters);
            return JdbcKit.executeUpdate(statement);
        } catch (Exception e) {
            throw new SQLRuntimeException(e);
        } finally {
            JdbcKit.close(statement);
        }
    }

    @Note("大数据量增删改执行逻辑")
    private long private_executeLargeUpdate(String sql, Object... parameters) {
        Connection connection = getConnection();
        PreparedStatement statement = null;
        try {
            statement = JdbcKit.newPreparedStatement(connection, sql, parameters);
            return JdbcKit.executeLargeUpdate(statement);
        } catch (Exception e) {
            throw new SQLRuntimeException(e);
        } finally {
            JdbcKit.close(statement);
        }
    }
}
